IF OBJECT_ID('trServerOSChangeLog','TR') IS NOT NULL
   DROP TRIGGER trServerOSChangeLog
GO
------------------------------------------------------------------------------------
--  TRIGGER NAME : dbo.trServerOSChangeLog
--   DESCRIPTION : Collects data deleted, inserted & updated from the ServerOS
--                 table.
------------------------------------------------------------------------------------
-- CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.04.2010  SYoung     Initial creation
------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[trServerOSChangeLog] ON [dbo].[ServerOS]
   FOR INSERT, UPDATE, DELETE
   
AS

SET NOCOUNT ON 

DECLARE @bit INT ,
        @field INT ,
        @maxfield INT ,
        @char INT ,
        @fieldname VARCHAR(128) ,
        @TableName VARCHAR(128) ,
        @PKCols VARCHAR(1000) ,
        @sql VARCHAR(7000), 
        @UpdateDate VARCHAR(21) ,
        @UserName VARCHAR(128),
        @HostName VARCHAR(128),
        @Type CHAR(1) ,
        @PKSelect VARCHAR(1000)

SELECT @TableName = 'ServerOS'

SELECT @UserName = SYSTEM_USER,
       @HostName = HOST_NAME(),
       @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) +' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

IF EXISTS (SELECT * FROM inserted)
   IF EXISTS (SELECT * FROM deleted)
      SELECT @Type = 'U'
   ELSE
      SELECT @Type = 'I'
ELSE
      SELECT @Type = 'D'

SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

SELECT @PKCols = COALESCE(@PKCols + ' AND', ' ON') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = pk.TABLE_NAME AND
       c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 WHERE pk.TABLE_NAME = @TableName AND
       CONSTRAINT_TYPE = 'PRIMARY KEY'

SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+CONVERT(varchar(100),COALESCE(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = pk.TABLE_NAME AND
       c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 WHERE pk.TABLE_NAME = @TableName AND
       CONSTRAINT_TYPE = 'PRIMARY KEY'

IF @PKCols IS NULL
   BEGIN
   RAISERROR('no PK on table %s', 16, -1, @TableName)
   RETURN
END

SELECT @field = 0,
       @maxfield = MAX(ORDINAL_POSITION) 
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @TableName

WHILE @field < @maxfield
      BEGIN
      SELECT @field = MIN(ORDINAL_POSITION) 
        FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_NAME = @TableName AND 
             ORDINAL_POSITION > @field
      SELECT @bit = (@field - 1 )% 8 + 1
      SELECT @bit = POWER(2,@bit - 1)
      SELECT @char = ((@field - 1) / 8) + 1

      IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
         BEGIN
         SELECT @fieldname = COLUMN_NAME 
           FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_NAME = @TableName AND
                ORDINAL_POSITION = @field

         SELECT @sql = 'INSERT TableChangeLog ( '+ CHAR(10)+
                       '       ExecutedAction,'+ CHAR(10)+
                       '       TableName,'+ CHAR(10)+
                       '       PK,'+ CHAR(10)+
                       '       FieldName,'+ CHAR(10)+
                       '       OldValue,'+ CHAR(10)+
                       '       NewValue,'+ CHAR(10)+
                       '       UpdateDate,'+ CHAR(10)+
                       '       UserName,'+ CHAR(10)+
                       '       ExecutedFrom)'+ CHAR(10)+
                       'SELECT '''+ @Type +''','+ CHAR(10)+
                       '       '''+ @TableName +''',' + @PKSelect +','+ CHAR(10)+
                       '       '''+ @fieldname + ''','+ CHAR(10)+
                       '        CONVERT(varchar(1000),d.' + @fieldname + '),'+ CHAR(10)+
                       '        CONVERT(varchar(1000),i.' + @fieldname + '),'+ CHAR(10)+
                       '        '''+ @UpdateDate +''','+ CHAR(10)+
                       '        '''+ @UserName +''','+ CHAR(10)+
                       '        '''+ @HostName +''''+ CHAR(10)+
                       '  FROM #ins i'+ CHAR(10)+
                       '  FULL OUTER JOIN #del d'+ @PKCols + CHAR(10)+
                       ' WHERE i.' + @fieldname + ' <> d.' + @fieldname + CHAR(10)+
                       '    OR (i.' + @fieldname + ' IS NULL AND  d.' + @fieldname + ' IS NOT NULL)' + CHAR(10)+
                       '    OR (i.' + @fieldname + ' IS NOT NULL AND  d.' + @fieldname + ' IS NULL)' 


         EXEC (@sql)
      END
END
GO

IF OBJECT_ID('trServerSQLChangeLog','TR') IS NOT NULL
   DROP TRIGGER trServerOSChangeLog
GO

------------------------------------------------------------------------------------
--  TRIGGER NAME : dbo.trServerSQLChangeLog
--   DESCRIPTION : Collects data deleted, inserted & updated from the ServerSQL
--                 table.
------------------------------------------------------------------------------------
-- CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.04.2010  SYoung     Initial creation
------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[trServerSQLChangeLog] ON [dbo].[ServerSQL]
   FOR INSERT, UPDATE, DELETE
   
AS

SET NOCOUNT ON 

DECLARE @bit INT ,
        @field INT ,
        @maxfield INT ,
        @char INT ,
        @fieldname VARCHAR(128) ,
        @TableName VARCHAR(128) ,
        @PKCols VARCHAR(1000) ,
        @sql VARCHAR(7000), 
        @UpdateDate VARCHAR(21) ,
        @UserName VARCHAR(128),
        @HostName VARCHAR(128),
        @Type CHAR(1) ,
        @PKSelect VARCHAR(1000)

SELECT @TableName = 'ServerSQL'

SELECT @UserName = SYSTEM_USER,
       @HostName = HOST_NAME(),
       @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) +' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

IF EXISTS (SELECT * FROM inserted)
   IF EXISTS (SELECT * FROM deleted)
      SELECT @Type = 'U'
   ELSE
      SELECT @Type = 'I'
ELSE
      SELECT @Type = 'D'

SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

SELECT @PKCols = COALESCE(@PKCols + ' AND', ' ON') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = pk.TABLE_NAME AND
       c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 WHERE pk.TABLE_NAME = @TableName AND
       CONSTRAINT_TYPE = 'PRIMARY KEY'

SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+CONVERT(varchar(100),COALESCE(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = pk.TABLE_NAME AND
       c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 WHERE pk.TABLE_NAME = @TableName AND
       CONSTRAINT_TYPE = 'PRIMARY KEY'

IF @PKCols IS NULL
   BEGIN
   RAISERROR('no PK on table %s', 16, -1, @TableName)
   RETURN
END

SELECT @field = 0,
       @maxfield = MAX(ORDINAL_POSITION) 
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @TableName

WHILE @field < @maxfield
      BEGIN
      SELECT @field = MIN(ORDINAL_POSITION) 
        FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_NAME = @TableName AND 
             ORDINAL_POSITION > @field
      SELECT @bit = (@field - 1 )% 8 + 1
      SELECT @bit = POWER(2,@bit - 1)
      SELECT @char = ((@field - 1) / 8) + 1

      IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
         BEGIN
         SELECT @fieldname = COLUMN_NAME 
           FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_NAME = @TableName AND
                ORDINAL_POSITION = @field

         SELECT @sql = 'INSERT TableChangeLog ( '+ CHAR(10)+
                       '       ExecutedAction,'+ CHAR(10)+
                       '       TableName,'+ CHAR(10)+
                       '       PK,'+ CHAR(10)+
                       '       FieldName,'+ CHAR(10)+
                       '       OldValue,'+ CHAR(10)+
                       '       NewValue,'+ CHAR(10)+
                       '       UpdateDate,'+ CHAR(10)+
                       '       UserName,'+ CHAR(10)+
                       '       ExecutedFrom)'+ CHAR(10)+
                       'SELECT '''+ @Type +''','+ CHAR(10)+
                       '       '''+ @TableName +''',' + @PKSelect +','+ CHAR(10)+
                       '       '''+ @fieldname + ''','+ CHAR(10)+
                       '        CONVERT(varchar(1000),d.' + @fieldname + '),'+ CHAR(10)+
                       '        CONVERT(varchar(1000),i.' + @fieldname + '),'+ CHAR(10)+
                       '        '''+ @UpdateDate +''','+ CHAR(10)+
                       '        '''+ @UserName +''','+ CHAR(10)+
                       '        '''+ @HostName +''''+ CHAR(10)+
                       '  FROM #ins i'+ CHAR(10)+
                       '  FULL OUTER JOIN #del d'+ @PKCols + CHAR(10)+
                       ' WHERE i.' + @fieldname + ' <> d.' + @fieldname + CHAR(10)+
                       '    OR (i.' + @fieldname + ' IS NULL AND  d.' + @fieldname + ' IS NOT NULL)' + CHAR(10)+
                       '    OR (i.' + @fieldname + ' IS NOT NULL AND  d.' + @fieldname + ' IS NULL)' 


         EXEC (@sql)
      END
END
GO
